import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class BinlogToSQL {

    // 列名与索引的映射关系
    private static final Map<Integer, String> COLUMN_MAP = new HashMap<>();

    static {
        COLUMN_MAP.put(1, "send_record_id");
        COLUMN_MAP.put(2, "uid");
        COLUMN_MAP.put(3, "recive_uid");
        COLUMN_MAP.put(4, "send_env");
        COLUMN_MAP.put(5, "room_uid");
        COLUMN_MAP.put(6, "room_type");
        COLUMN_MAP.put(7, "gift_id");
        COLUMN_MAP.put(8, "gift_type");
        COLUMN_MAP.put(9, "gift_num");
        COLUMN_MAP.put(10, "total_gold_num");
        COLUMN_MAP.put(11, "total_diamond_num");
        COLUMN_MAP.put(12, "recive_guild_id");
        COLUMN_MAP.put(13, "create_time");
        COLUMN_MAP.put(14, "send_time");
        COLUMN_MAP.put(15, "current_week");
        COLUMN_MAP.put(16, "bath_uuid");
        COLUMN_MAP.put(17, "send_mode");
        COLUMN_MAP.put(18, "gift_total_diamond_num");
        COLUMN_MAP.put(19, "intimate_pay_giver_uid");
        COLUMN_MAP.put(20, "correlation_record_id");
    }

    public static void main(String[] args) {
        String inputFile = "C:\\Users\\Administrator\\Desktop\\data_cover\\000851.sql";
        String outputFile = "C:\\Users\\Administrator\\Desktop\\data_cover\\000851_back.sql";

        try (BufferedReader reader = new BufferedReader(new InputStreamReader(
                new FileInputStream(inputFile), "UTF-8"));  // 假设是小端字节序的 UCS-2/UTF-16
             BufferedWriter writer = new BufferedWriter(new FileWriter(outputFile))) {

            List<String> currentTransaction = new ArrayList<>();
            boolean inTransaction = false;
            StringBuilder insertStatement = new StringBuilder();

            String line;
            while ((line = reader.readLine()) != null) {
//                System.out.println(line);
                if (line.contains("### INSERT INTO `tiaoshi`.`gift_send_record`")) {
                    if (inTransaction && !insertStatement.toString().trim().isEmpty()) {
                        // Write the previous transaction before starting a new one
                        writeTransaction(insertStatement, writer);
                    }
                    inTransaction = true;
                    insertStatement = new StringBuilder("INSERT INTO `tiaoshi`.`gift_send_record` SET\n");
                } else if (inTransaction) {
                    if (line.startsWith("# at") || line.startsWith("#250222")) {
                        // Ignore metadata lines
                        continue;
                    }
                    if (line.contains("### END") || line.contains("### UPDATE") || line.contains("### DELETE")) {
                        // End of transaction or other table operations
                        if (!insertStatement.toString().trim().isEmpty()) {
                            writeTransaction(insertStatement, writer);
                        }
                        inTransaction = false;
                    } else if (line.startsWith("###   @")) {
                        // Parse and format the row data
                        String formattedLine = parseRowData(line);
                        insertStatement.append(formattedLine).append("\n");
                    }
                }
            }

            // Write any remaining transaction
            if (inTransaction && !insertStatement.toString().trim().isEmpty()) {
                writeTransaction(insertStatement, writer);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String parseRowData(String line) {
        // Remove the '### ' prefix
        String trimmedLine = line.substring(4);

        // Split into parts
        String[] parts = trimmedLine.split("=");
        int index = Integer.parseInt(parts[0].substring(3)); // Extract the number after @
        String columnName = COLUMN_MAP.get(index);
        String value = parts[1];

        // Handle NULL values
        if ("NULL".equalsIgnoreCase(value)) {
            return "`" + columnName + "` = NULL,";
        }

        // Handle timestamp fields
        if ("create_time".equals(columnName) || "send_time".equals(columnName)) {

            String[] times = trimmedLine.split("=");
            String time2 = times[1];
            String[] split = time2.split("\\.");
            long timestamp;
            if (split.length > 0) {
                timestamp = Long.parseLong(split[0]);
            } else {
                timestamp = Long.parseLong(time2);
            }

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sdf.setTimeZone(TimeZone.getTimeZone("Asia/Riyadh")); // 设置为阿拉伯时区
            String formattedTimestamp = sdf.format(new Date(timestamp * 1000L));
            return "`" + columnName + "` = '" + formattedTimestamp + "',";
        }

        // Handle string values
        if (value.startsWith("'") && value.endsWith("'")) {
            return "`" + columnName + "` = " + value + ",";
        }

        // Handle numeric values
        return "`" + columnName + "` = " + value + ",";
    }

    private static void writeTransaction(StringBuilder insertStatement, BufferedWriter writer) throws IOException {
        // Remove trailing comma from the last line
        String finalStatement = insertStatement.toString().trim();
        if (finalStatement.endsWith(",")) {
            finalStatement = finalStatement.substring(0, finalStatement.length() - 1);
        }
        finalStatement += ";";
        writer.write(finalStatement);
        writer.newLine();
        writer.newLine();
    }
}